package backend.dal.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import backend.dal.DALException;
import backend.dal.UserDAO;
import backend.model.User;
import backend.util.PropertyProvider;

public class JdbcUserDAO implements UserDAO {

	private final ConnectionManager cm;
	private static final Logger LOG = LoggerFactory
			.getLogger(JdbcUserDAO.class);

	public JdbcUserDAO() {
		cm = ConnectionManager.getInstance();
	}

	@Override
	public User getUserById(int id) throws DALException {
		User user = new User();
		Connection con = null;
		try {
			con = cm.getConnection();
			String strSQL = "SELECT * FROM user WHERE userId = '" + id + "'";
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(strSQL);
			while (rs.next()) {
				user.setUserId(rs.getInt("userId"));
				user.setName(rs.getString("name"));
				user.setFirstName(rs.getString("firstName"));
				user.setMobileNr(rs.getString("mobileNr"));
				user.setEmail(rs.getString("email"));
				user.setFunction(rs.getString("function"));
				user.setUsername(rs.getString("userName"));
				user.setRole(rs.getString("role"));
			}
			LOG.info("User succesfully selected");
		} catch (SQLException e) {
			LOG.error("Unsuccesfull user selection", e);
			throw new DALException("User selection failed", e);
		} finally {
			if (con != null) {
				cm.returnConnection(con);
			}
		}
		return user;
	}

	@Override
	public void insertUser(User user) throws DALException {
		Connection con = null;
		try {
			con = cm.getConnection();
			String insertTableSQL = "INSERT INTO user"
					+ "(Name, firstName, mobileNr, email, function, userName, password, role) VALUES"
					+ "(?,?,?,?,?,?,?,?)";
			PreparedStatement preparedStatement = con
					.prepareStatement(insertTableSQL);
			preparedStatement.setString(1, user.getName());
			preparedStatement.setString(2, user.getFirstName());
			preparedStatement.setString(3, user.getMobileNr());
			preparedStatement.setString(4, user.getEmail());
			preparedStatement.setString(5, user.getFunction());
			preparedStatement.setString(6, user.getUsername());
			preparedStatement.setString(7, user.getPassword());
			preparedStatement.setString(8, user.getRole());

			preparedStatement.executeUpdate();
			LOG.info("User succesfully inserted");
		} catch (SQLException e) {
			LOG.error("User insertion failed", e);
			throw new DALException("User insertion failed", e);
		} finally {
			if (con != null) {
				cm.returnConnection(con);
			}
		}

	}

	@Override
	public void updateUser(User user) throws DALException {
		Connection con = null;
		try {
			con = cm.getConnection();
			String insertTableSQL = "UPDATE user"
					+ " SET name = ?, firstName = ?, mobileNr = ?, email = ?, function = ?, userName = ?, password =?, role = ?"
					+ " WHERE userId = ?";

			PreparedStatement preparedStatement = con
					.prepareStatement(insertTableSQL);
			preparedStatement.setString(1, user.getName());
			preparedStatement.setString(2, user.getFirstName());
			preparedStatement.setString(3, user.getMobileNr());
			preparedStatement.setString(4, user.getEmail());
			preparedStatement.setString(5, user.getFunction());
			preparedStatement.setString(6, user.getUsername());
			preparedStatement.setString(7, user.getPassword());
			preparedStatement.setString(8, user.getRole());
			preparedStatement.setInt(9, user.getUserId());

			preparedStatement.executeUpdate();
			LOG.info("User succesfully updated");
		} catch (SQLException e) {
			LOG.error("User update failed", e);
			throw new DALException("User update failed", e);
		} finally {
			if (con != null) {
				cm.returnConnection(con);
			}
		}
	}

	@Override
	public void deleteUser(Integer id) throws DALException {
		Connection con = null;
		try {
			con = cm.getConnection();
			String strSQL = "DELETE FROM user WHERE userId = ?";
			PreparedStatement preparedStatement = con.prepareStatement(strSQL);
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();
			LOG.info("User succesfully deleted");
		} catch (SQLException e) {
			LOG.error("User deletion failed", e);
			throw new DALException("User deletion failed", e);
		} finally {
			if (con != null) {
				cm.returnConnection(con);
			}
		}

	}

	@Override
	public ArrayList<User> viewAllUsers() throws DALException {
		final ArrayList<User> users = new ArrayList<User>();
		Connection con = null;
		try {
			con = cm.getConnection();
			String SQL = "SELECT * FROM User";
			PreparedStatement preparedStatement = con.prepareStatement(SQL);
			ResultSet rs = preparedStatement.executeQuery(SQL);
			while (rs.next()) {
				User user = new User();
				user.setUserId(rs.getInt("userId"));
				user.setName(rs.getString("name"));
				user.setFirstName(rs.getString("firstName"));
				user.setMobileNr(rs.getString("mobileNr"));
				user.setEmail(rs.getString("email"));
				user.setFunction(rs.getString("function"));
				user.setUsername(rs.getString("userName"));
				user.setRole(rs.getString("role"));
				users.add(user);
			}
			LOG.info("Users displayed");
		} catch (SQLException e) {
			LOG.error("Users display failed", e);
			throw new DALException("Users display failed", e);
		} finally {
			if (con != null) {
				cm.returnConnection(con);
			}
		}
		return users;
	}

	@Override
	public String getUserPassword(Integer id) throws DALException {
		Connection con = null;
		String password = "";
		try {
			con = cm.getConnection();
			String strSQL = "SELECT password FROM user WHERE userId = '" + id
					+ "'";
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(strSQL);
			while (rs.next()) {
				password = rs.getString("password");
			}
		} catch (SQLException e) {
			LOG.error("Password retrieve failed", e);
			throw new DALException("Password retrieve failed", e);
		} finally {
			if (con != null) {
				cm.returnConnection(con);
			}
		}
		return password;
	}

}
